
#pragma nodebook off
#Use nodebook for better reproducibility https://github.com/uoa-eResearch/nodebook
%reload_ext nodebook.ipython
%nodebook disk phase1


# load libraries
import geopandas as gpd # vector data
import pandas as pd # tabular data, loading CSVs
import numpy as np # numeric data
from util import *
import matplotlib # plotting
import contextily as ctx # Used for contextual basemaps
from scipy.spatial import cKDTree
from matplotlib_scalebar.scalebar import ScaleBar # scalebar for plot
import matplotlib.pyplot as plt # plotting
from tqdm.auto import tqdm # progress bars
tqdm.pandas()
import requests
import requests_cache
requests_cache.install_cache('requests_cache')
import json
from shapely.geometry import Point, shape, LineString, MultiLineString, GeometryCollection, MultiPoint, Polygon  # creating points
plt.rcParams['figure.figsize'] = (20, 20)
pd.set_option('max_columns', None)
pd.set_option('max_rows', 300)


%%time
df = pd.read_csv("restricted/BCs_issued_by_AUP_TLADCs_2021FEB.csv", encoding='cp1252')
df = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.X_Coordinate, df.Y_Coordinate), crs=4326)

CPU times: user 1.24 s, sys: 210 ms, total: 1.45 s
Wall time: 1.46 s

df


df["Ranged_Address_indicator"] = df.ADDRESS_1.str.contains('\d\w?\s?-', regex=True, na=False)
df["Ranged_Address_indicator"].value_counts()

False    200848
True       6378
Name: Ranged_Address_indicator, dtype: int64


%%time
# get number and name of street (but not 'road', 'street', 'place' etc)
# this can be used to match addresses with building consents

def number_name_bc(x):
    """extract street number and first complete word of the street name from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3]]).lower()
        return ' '.join(joined_address.split(' ')[:2])

def full_address_bc(x):
    """extract full address from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3] if not str(x[f'ADDRESS_{i}']) == 'nan']).lower()
        return joined_address

df['number_name'] = df.apply(number_name_bc, axis=1)
df['full_address'] = df.apply(full_address_bc, axis=1)

CPU times: user 51.3 s, sys: 1.53 s, total: 52.9 s
Wall time: 52.9 s


any(df.full_address.str.contains("[āēīōū]"))

False


parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb').to_crs(2193)
parcels = parcels.set_index("id")
parcels


addresses = gpd.read_file('input/lds-nz-addresses-pilot-FGDB.zip!nz-addresses-pilot.gdb').to_crs(2193)
addresses


def number_name_addresses(x):
    return ' '.join(x.split(' ')[:2]).lower()
addresses['address_number_name'] = addresses.full_address_ascii.apply(number_name_addresses)
addresses


%%time
parcels_addressed = gpd.sjoin(parcels, addresses)

CPU times: user 34.4 s, sys: 4.23 s, total: 38.6 s
Wall time: 38.5 s


%%time
parcels["addresses"] = parcel_address_number_name = parcels_addressed.groupby('id')['address_number_name'].apply(list)
display(parcels["addresses"])
parcels["full_addresses"] = parcels_addressed.groupby('id')["full_address"].apply(list)
display(parcels["full_addresses"])

id
4918991    [3/12 college, 1/12 college, 2/12 college, 12 ...
4919914                                         [15 abraham]
4926734    [3/20 simkin, 1/20 simkin, 2/20 simkin, 20 sim...
4926945    [3 abraham, 4/3 abraham, 3/3 abraham, 2/3 abra...
4929034                                         [49 caulton]
                                 ...                        
5227472                                        [112z bairds]
5242816                                                  NaN
7520053                                        [9 lovegrove]
7520049                                       [15 lovegrove]
4733845                                            [7 otara]
Name: addresses, Length: 547903, dtype: object

id
4918991    [3/12 College Road, Saint Johns, Auckland, 1/1...
4919914            [15 Abraham Place, Saint Johns, Auckland]
4926734    [3/20 Simkin Avenue, Saint Johns, Auckland, 1/...
4926945    [3 Abraham Place, Saint Johns, Auckland, 4/3 A...
4929034           [49 Caulton Street, Saint Johns, Auckland]
                                 ...                        
5227472                  [112Z Bairds Road, Ōtara, Auckland]
5242816                                                  NaN
7520053              [9 Lovegrove Crescent, Ōtara, Auckland]
7520049             [15 Lovegrove Crescent, Ōtara, Auckland]
4733845                      [7 Otara Road, Ōtara, Auckland]
Name: full_addresses, Length: 547903, dtype: object

CPU times: user 34.9 s, sys: 6.33 ms, total: 34.9 s
Wall time: 34.9 s


%%time
df = gpd.sjoin(df.to_crs(2193), parcels[["addresses","full_addresses","geometry"]], how="left")

CPU times: user 11.3 s, sys: 95.1 ms, total: 11.4 s
Wall time: 11.4 s


print(sum(df.number_name.str.contains("mt")),
sum(df.number_name.str.contains("mount")),
sum(addresses.address_number_name.str.contains("mt")),
sum(addresses.address_number_name.str.contains("mount")))

690 597 135 5840


df = df.rename(columns={"index_right": "LINZ_parcel_ID"})
df.LINZ_parcel_ID = df.LINZ_parcel_ID.astype("Int64")
df


assert df.index.is_unique


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = df[~pd.isna(df.number_name) & ~pd.isna(df.addresses)].apply(check_match, axis=1)
df["LINZ_MATCH_CODE"] = match
df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    127623
NaN     79603
Name: LINZ_MATCH_CODE, dtype: int64


parcel_tree = cKDTree(list(parcels.centroid.apply(lambda x: (x.x, x.y))))
parcel_tree

<scipy.spatial.ckdtree.cKDTree at 0x7f9063420510>


sample = df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(addresses.address_number_name)].head(1)
sample


indices_in_range = parcel_tree.query_ball_point([sample.centroid.iloc[0].x, sample.centroid.iloc[0].y], 1250)
ax = sample.to_crs(epsg=3857).plot(color="red", alpha=1)
sample.buffer(1250).to_crs(epsg=3857).plot(ax=ax, alpha=.5, color="green")
potential_matches = parcels.iloc[indices_in_range]
potential_matches.to_crs(epsg=3857).plot(ax=ax, alpha=.5, edgecolor="black")
potential_matches[potential_matches.addresses.str.contains(sample.number_name.iloc[0], na=False, regex=False)].to_crs(epsg=3857).plot(ax=ax, color="red")
ctx.add_basemap(ax, source=ctx.providers.Esri.WorldImagery)


def find_match_in_range(row):
    indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], 1250)
    potential_matches = parcels.iloc[indices_in_range]
    match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
    if len(match):
        row.LINZ_parcel_ID = match.index[0]
        row.addresses = match.addresses.iloc[0]
        row.full_addresses = match.full_addresses.iloc[0]
        row.LINZ_MATCH_CODE = 2
    return row
matches = df[
    ~pd.isna(df.number_name) &
    pd.isna(df["LINZ_MATCH_CODE"]) &
    df.number_name.isin(addresses.address_number_name)].progress_apply(find_match_in_range, axis=1)


matches


df.update(matches)
df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    127623
NaN     43953
2.0     35650
Name: LINZ_MATCH_CODE, dtype: int64


df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(addresses.address_number_name)]


%%time
number_name_lookup = {}
for parcel_id, addresses in parcels.addresses[~pd.isna(parcels.addresses)].iteritems():
    for address in addresses:
        if address not in number_name_lookup:
            number_name_lookup[address] = []
        number_name_lookup[address].append(parcel_id)

CPU times: user 1.78 s, sys: 0 ns, total: 1.78 s
Wall time: 1.77 s


%%time
def find_text_match(row):
    matching_ids = number_name_lookup.get(row.number_name)
    if len(matching_ids):
        match = parcels.loc[matching_ids]
        closest_parcel_id = match.distance(row.geometry).idxmin()
        row.LINZ_parcel_ID = closest_parcel_id
        row.addresses = match.addresses[closest_parcel_id]
        row.full_addresses = match.full_addresses[closest_parcel_id]
        row.LINZ_MATCH_CODE = 3
    return row
matches = df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
df.update(matches)
df.LINZ_MATCH_CODE.value_counts(dropna=False)

CPU times: user 29.7 s, sys: 378 ms, total: 30.1 s
Wall time: 29.8 s

1.0    127623
NaN     38354
2.0     35650
3.0      5599
Name: LINZ_MATCH_CODE, dtype: int64


df.Ranged_Address_indicator = df.Ranged_Address_indicator.astype(bool)


maybe_subdivision = df[df.number_name.str.contains("/") & pd.isna(df.LINZ_MATCH_CODE) & ~df.Ranged_Address_indicator]
maybe_subdivision


maybe_subdivision.number_name = maybe_subdivision.number_name.str.split("/").str[-1]

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
maybe_subdivision.LINZ_MATCH_CODE = match
match.value_counts(dropna=False)

1.0    730
NaN    692
dtype: int64


matches = maybe_subdivision[
    pd.isna(match) &
    maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


matches


maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)

1.0    730
NaN    643
2.0    223
Name: LINZ_MATCH_CODE, dtype: int64


matches = maybe_subdivision[pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    730
NaN    496
2.0    223
3.0    147
Name: LINZ_MATCH_CODE, dtype: int64


maybe_subdivision


maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
maybe_subdivision.LINZ_MATCH_CODE.value_counts()

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)

4.0    730
5.0    223
6.0    147
Name: LINZ_MATCH_CODE, dtype: int64


df.update(maybe_subdivision)


maybe_subdivision = df[
    df.number_name.str.contains("[0-9][a-zA-Z] ") &
    pd.isna(df.LINZ_MATCH_CODE) &
    (df.Ranged_Address_indicator == False)
]
maybe_subdivision


def strip_char(s):
    number, name = s.split()
    number = "".join([c for c in number if c.isnumeric()])
    return number + " " + name
maybe_subdivision.number_name = maybe_subdivision.number_name.apply(strip_char)

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
maybe_subdivision.LINZ_MATCH_CODE = match
match.value_counts(dropna=False)

NaN    924
1.0    699
dtype: int64


matches = maybe_subdivision[
    pd.isna(match) &
    maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    795
1.0    699
2.0    465
Name: LINZ_MATCH_CODE, dtype: int64


matches = maybe_subdivision[pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    699
2.0    465
NaN    442
3.0    353
Name: LINZ_MATCH_CODE, dtype: int64


maybe_subdivision


maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
maybe_subdivision.LINZ_MATCH_CODE.value_counts()

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)

4.0    699
5.0    465
6.0    353
Name: LINZ_MATCH_CODE, dtype: int64


df.update(maybe_subdivision)


%%time
distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
distances

CPU times: user 1.63 s, sys: 0 ns, total: 1.63 s
Wall time: 1.64 s

0           0.000000
1           0.000000
2           0.000000
3         272.107738
4         302.608731
             ...    
207221      0.000000
207222      0.000000
207223     34.221509
207224      0.000000
207225      0.000000
Length: 207054, dtype: float64


distances[distances>0].describe()

count     42437.000000
mean       2382.699198
std        7915.549676
min           0.000062
25%           9.451613
50%          27.518422
75%         191.588766
max      100594.158959
dtype: float64


df["distance_to_parcel"] = distances


df[["number_name", "addresses"]]


def check_street_match(row):
    if row.number_name and len(row.number_name)>1:
        street = row.number_name.split()[-1]
        if street in str(row.addresses):
            return 7
    return np.nan
street_matches = df[pd.isna(df.LINZ_MATCH_CODE)].progress_apply(check_street_match, axis=1)
display(street_matches.value_counts(dropna=False))
df.LINZ_MATCH_CODE.update(street_matches)

NaN    25328
7.0    10409
dtype: int64


df.LINZ_MATCH_CODE[~pd.isna(df.LINZ_parcel_ID) & pd.isna(df.LINZ_MATCH_CODE)] = 8

/tmp/ipykernel_838276/8225981.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df.LINZ_MATCH_CODE[~pd.isna(df.LINZ_parcel_ID) & pd.isna(df.LINZ_MATCH_CODE)] = 8


df.LINZ_MATCH_CODE = df.LINZ_MATCH_CODE.fillna(9)
df.LINZ_MATCH_CODE.value_counts()

1.0    127623
2.0     35650
8.0     25212
7.0     10409
3.0      5599
4.0      1429
5.0       688
6.0       500
9.0       116
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_MATCH_CODE[df.Ranged_Address_indicator].value_counts()

7.0    3295
8.0    2199
1.0     392
2.0     388
3.0     103
9.0       1
Name: LINZ_MATCH_CODE, dtype: int64


ranged = df[df.Ranged_Address_indicator & (df.LINZ_MATCH_CODE > 2)]
ranged


def parse_int(value):
    return int(''.join(filter(lambda c: c.isdigit(), value)))

def char_range(c1, c2):
    """Generates the characters from `c1` to `c2`, inclusive."""
    for c in range(ord(c1), ord(c2)+1):
        yield chr(c)

def range_expand(r):
    """given a range r, like '20-23', expand the range on one side of the road, e.g. ['20', '21', '22', '23']"""
    if '/' in r:
        a, b = r.split('/')
        if "-" in a:
            r = a
            suffix = "/" + b
        else:
            r = b
            suffix = "/" + a
    else:
        suffix = ''
    r1, r2 = r.split('-')
    if r2 == "sep" or r2 == "":
        return [r1]
    if r2.isalpha():
        return [r1.replace(r1[-1], char) for char in char_range(r1[-1], r2)]
    r1 = parse_int(r1)
    r2 = parse_int(r2)
    if suffix:
        step = 1
    else:
        step = 2
    if r1 < r2:
        return [str(i) + suffix for i in range(r1, r2 + 1, step)]
    else:
        return [str(i) + suffix for i in range(r2, r1 + 1, step)]

corrections = {
    "1/ 231-235": "231-235 hinemoa",
    "2/ 16-18": "16-18 montgomery",
    "2/ 2-8": "2-8 matipo",
    "r 35-41": "35-41 birkenhead",
    "r 31-35": "31-35 cresta",
    "27 -": "27-31 victoria",
    "1 &": "2-1/96 bernleigh",
    "55121-23 morrison": "21-23 morrison",
    "1954-60 great": "1954-1960 great",
    "3-9 ": "3-9 faldo"
}
ranged.number_name = ranged.number_name.replace(corrections)

for r in ranged.number_name:
    e = None
    try:
        number, name = r.split()
        range_expanded = range_expand(number)
        assert len(range_expanded) > 0 and len(range_expanded) < 1000
    except Exception as e:
        print(f"Parsing of {r} failed: {e}")
        display(ranged[ranged.number_name == r])

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)


for test_case in ['20-28', '1-5/32a', '53a-c']:
    print(f"{test_case}: {range_expand(test_case)}")

20-28: ['20', '22', '24', '26', '28']
1-5/32a: ['1/32a', '2/32a', '3/32a', '4/32a', '5/32a']
53a-c: ['53a', '53b', '53c']


%%time
expanded_ranged_addresses = []
for i, sample in tqdm(ranged.iterrows(), total=len(ranged)):
    # Expand this ranged address into it's own dataframe, with each number as it's own row
    number, name = sample.number_name.split()
    rows = []
    for n in range_expand(number):
        row = sample.copy()
        row.number_name = n + " " + name
        row["unique_id"] = f"{row.OBS}_{n}"
        rows.append(row)
    sample = pd.DataFrame(rows).set_index("unique_id")
    if not sample.index.is_unique:
        display(sample)
        raise

    # Step 1
    def check_match(row):
        if row.number_name in row.addresses:
            return 1
        return np.nan
    match = sample[~pd.isna(sample.number_name) & ~pd.isna(sample.addresses)].apply(check_match, axis=1)
    if len(match):
        sample["LINZ_MATCH_CODE"] = match
    else:
        sample["LINZ_MATCH_CODE"] = np.nan

    #However, we set r in step 2 to a larger number in order to do a wider search.

    def find_match_in_range(row):
        if str(row.geometry) == "nan":
            return row
        indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], 2000)
        potential_matches = parcels.iloc[indices_in_range]
        match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
        if len(match):
            row.LINZ_parcel_ID = match.index[0]
            row.addresses = match.addresses.iloc[0]
            row.full_addresses = match.full_addresses.iloc[0]
            row.LINZ_MATCH_CODE = 2
        return row
    matches = sample[
        ~pd.isna(sample.number_name) &
        pd.isna(sample["LINZ_MATCH_CODE"])].apply(find_match_in_range, axis=1)
    sample.update(matches)

    # If no matches are found at step 2, but at least one match in the ranged address is found at step 1 or step 2, stop and set LINZ_match_code to 10
    # This avoids matching parcels that are far away from the rest of the group
    if all(pd.isna(sample.LINZ_MATCH_CODE)): # all na here means no matches in step 1 or 2
        # Only if there is no match within the ranged addresses at either step 1 or step 2 do we proceed to step 3. 
        matches = sample[pd.isna(sample["LINZ_MATCH_CODE"]) & sample.number_name.isin(number_name_lookup.keys())].apply(find_text_match, axis=1)
        sample.update(matches)
        maybe_subdivision = sample[sample.number_name.str.contains("/") & pd.isna(sample.LINZ_MATCH_CODE)]
        if len(maybe_subdivision):
            maybe_subdivision.number_name = maybe_subdivision.number_name.str.split("/").str[-1]
            # step 1
            match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
            if len(match):
                maybe_subdivision["LINZ_MATCH_CODE"] = match
            else:
                maybe_subdivision["LINZ_MATCH_CODE"] = np.nan
            # step 2
            matches = maybe_subdivision[
                pd.isna(match) &
                maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_match_in_range, axis=1)
            maybe_subdivision.update(matches)
            # step 3
            matches = maybe_subdivision[
                pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_text_match, axis=1)
            maybe_subdivision.update(matches)
            maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
            sample.update(maybe_subdivision)
        maybe_subdivision = sample[
            sample.number_name.str.contains("[0-9][a-zA-Z] ") &
            pd.isna(sample.LINZ_MATCH_CODE)
        ]
        if len(maybe_subdivision):
            maybe_subdivision.number_name = maybe_subdivision.number_name.apply(strip_char)
            # step 1
            match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
            if len(match):
                maybe_subdivision["LINZ_MATCH_CODE"] = match
            else:
                maybe_subdivision["LINZ_MATCH_CODE"] = np.nan
            # step 2
            matches = maybe_subdivision[
                pd.isna(match) &
                maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_match_in_range, axis=1)
            maybe_subdivision.update(matches)
            # step 3
            matches = maybe_subdivision[
                pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_text_match, axis=1)
            maybe_subdivision.update(matches)
            maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
            sample.update(maybe_subdivision)

        if all(pd.isna(sample.LINZ_MATCH_CODE)):
            street_matches = sample[pd.isna(sample.LINZ_MATCH_CODE)].apply(check_street_match, axis=1)
            sample.LINZ_MATCH_CODE.update(street_matches)
            sample.loc[~pd.isna(sample.LINZ_parcel_ID) & pd.isna(sample.LINZ_MATCH_CODE), "LINZ_MATCH_CODE"] = 8
            sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(9)
        else:
            sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(10)
    else:
        sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(10)
    #We include an additional flag for each ranged address that indicates the best (i.e. lowest) LINZ_MATCH_CODE for that range.
    #E.g. for a ranged address with five addresses, if at least one of them had LINZ_MATCH_CODE = 2,
    #and all others were LINZ_MATCH_CODE > 2, then the best LINZ_MATCH_CODE would be 2. This is call LINZ_2ND_MATCH_CODE below.
    # Each address (in the ranged address set) is assigned its own unique LINZ parcel.
    # Each parcel is then assigned the information given in 1 through 11 below.
    # Because the parcels share the same consent ID, we can tell that the different parcels pertain to the same consent, thereby avoiding double counting. 
    sample["LINZ_2ND_MATCH_CODE"] = sample.LINZ_MATCH_CODE.min()
    expanded_ranged_addresses.append(sample)

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<timed exec>:85: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
<timed exec>:58: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
<timed exec>:83: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

CPU times: user 23min 9s, sys: 3.76 s, total: 23min 13s
Wall time: 23min 9s


df = df.drop(ranged.index)
df = pd.concat([df] + expanded_ranged_addresses)
df


df.LINZ_MATCH_CODE[df.Ranged_Address_indicator].value_counts(dropna=False)

2.0     15548
10.0    13305
1.0      3906
3.0      2772
8.0      2263
7.0       804
5.0       125
4.0        45
6.0        35
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_2ND_MATCH_CODE[df.Ranged_Address_indicator].value_counts(dropna=False)

2.0    16078
1.0    14831
3.0     3850
8.0     2263
7.0      804
NaN      780
5.0      123
4.0       43
6.0       31
Name: LINZ_2ND_MATCH_CODE, dtype: int64


df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0     131137
2.0      50810
8.0      25276
10.0     13305
3.0       8268
7.0       7918
4.0       1474
5.0        813
6.0        535
9.0        115
Name: LINZ_MATCH_CODE, dtype: int64


%%time
df = df.reset_index()
distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
display(distances)
display(distances[distances>0].describe())

<timed exec>:2: UserWarning: CRS mismatch between the CRS of left geometries and the CRS of right geometries.
Use `to_crs()` to reproject one of the input geometries to match the CRS of the other.

Left CRS: None
Right CRS: EPSG:2193

0           0.000000
1           0.000000
2           0.000000
3         272.107738
4         302.608731
             ...    
239646      0.000000
239647     12.431048
239648      0.000000
239649      0.000000
239650      0.000000
Length: 239480, dtype: float64

count     60541.000000
mean       2816.291424
std        8732.070907
min           0.000062
25%          11.051819
50%          40.195130
75%         237.302443
max      100594.158959
dtype: float64

CPU times: user 2.41 s, sys: 240 ms, total: 2.65 s
Wall time: 2.65 s


df["distance_to_parcel"] = distances


df[(df.LINZ_MATCH_CODE == 3) & (df.distance_to_parcel > 10000)]


%%time
def fix_full_address(row):
    number_name = ' '.join(row.full_address.split(' ')[:2])
    row.full_address = row.full_address.replace(number_name, row.number_name)
    return row
df = df.progress_apply(fix_full_address, axis=1)
df

CPU times: user 1min 16s, sys: 999 ms, total: 1min 17s
Wall time: 1min 17s


session = requests.Session()
def geocode(address):
    result = session.get("https://api-proxy.auckland-cer.cloud.edu.au/nominatim/search.php?q=" + address).json()
    if result:
        return pd.Series(result[0])
    else:
        return pd.Series()
bad_matches = df.full_address[df.LINZ_MATCH_CODE.isin([3,6,7,8,9,10])]
geocoding_results = bad_matches.progress_apply(geocode)
geocoding_results

/tmp/ipykernel_838276/736219875.py:7: DeprecationWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  return pd.Series()


geocoding_results = geocoding_results.dropna()
display(geocoding_results.category.value_counts())
display(geocoding_results.type.value_counts())

highway      31463
place         7627
leisure       1050
amenity        819
building       408
natural        323
landuse        292
man_made       238
shop           133
railway        120
waterway       119
tourism         71
boundary        68
aeroway         66
bridge          24
office          18
craft            6
historic         2
junction         2
club             1
emergency        1
Name: category, dtype: int64

residential               10289
secondary                  7607
house                      6440
tertiary                   6202
unclassified               3330
primary                    3055
park                       1014
suburb                      426
school                      303
town                        281
yes                         252
living_street               236
motorway                    231
village                     188
bus_stop                    173
monitoring_station          170
trunk                       151
hamlet                      147
place_of_worship            140
bay                         134
locality                    106
motorway_junction           100
island                       88
construction                 87
retail                       80
station                      72
river                        70
peak                         63
administrative               62
aerodrome                    55
service                      45
community_centre             43
emergency_access_point       40
university                   38
stream                       36
fire_station                 34
restaurant                   34
pedestrian                   34
proposed                     33
motel                        32
beach                        32
water_well                   31
apartments                   29
cliff                        28
hospital                     27
convenience                  27
doctors                      26
marae                        26
industrial                   26
mall                         25
recreation_ground            25
wood                         21
cemetery                     20
path                         19
fuel                         17
golf_course                  16
library                      15
hotel                        15
kindergarten                 15
fast_food                    15
pub                          14
ferry_terminal               14
attraction                   13
hairdresser                  12
water                        12
cinema                       12
parking                      11
surveillance                 10
bicycle                      10
sports_centre                10
bar                          10
pier                          9
supermarket                   9
bare_rock                     9
car                           9
viewpoint                     9
track                         9
volcano                       9
commercial                    8
milestone                     8
taxiway                       8
cafe                          7
cape                          7
neighbourhood                 7
health_food                   7
detached                      7
waterfall                     7
car_rental                    6
office                        6
protected_area                6
footway                       6
garden_furniture              6
stop                          6
childcare                     6
events_venue                  5
bridge                        5
pumping_station               5
strait                        5
yard                          5
travel_agency                 5
caravan_site                  5
chalet                        5
townhall                      4
fitness_centre                4
tidal_channel                 4
sailmaker                     4
tower                         4
theatre                       4
post_office                   4
wastewater_plant              4
greenhouse                    3
coworking                     3
department_store              3
courthouse                    3
platform                      3
conservation                  3
association                   3
dentist                       3
horse_riding                  3
pitch                         3
government                    3
estate_agent                  3
company                       3
gate                          2
police                        2
party                         2
manor                         2
boatbuilder                   2
social_facility               2
basin                         2
veterinary                    2
clothes                       2
mountain_range                2
boat_storage                  2
tyres                         2
bus_station                   2
furniture                     2
farm                          2
waste_transfer_station        1
cycleway                      1
diplomatic                    1
greengrocer                   1
water_sports                  1
accountant                    1
doityourself                  1
variety_store                 1
clinic                        1
outdoor                       1
parking_space                 1
forest                        1
shelter                       1
religious                     1
arts_centre                   1
electronics                   1
wine                          1
planned                       1
loading_dock                  1
grandstand                    1
dam                           1
money_lender                  1
parking_position              1
farmland                      1
quarry                        1
guest_house                   1
railway                       1
landfill                      1
dock                          1
tree                          1
sport                         1
ambulance_station             1
islet                         1
reservoir                     1
research_institute            1
Name: type, dtype: int64


geocoding_results = gpd.GeoDataFrame(geocoding_results, geometry=gpd.points_from_xy(geocoding_results.lon, geocoding_results.lat), crs=4326).to_crs(2193)
geocoding_results


df["distance_from_geocoding_result"] = df.loc[geocoding_results.index].distance(geocoding_results)
df["distance_from_geocoding_result"].describe()

count    4.285100e+04
mean     8.331929e+03
std      6.255889e+04
min      0.000000e+00
25%      1.341384e+02
50%      4.701403e+02
75%      1.280151e+03
max      1.728650e+06
Name: distance_from_geocoding_result, dtype: float64


%%time
# redo join with updated coordinates
geocoding_results = gpd.sjoin(geocoding_results, parcels[["addresses","geometry"]], how="left")

CPU times: user 2.51 s, sys: 88 ms, total: 2.6 s
Wall time: 2.6 s


geocoding_results = geocoding_results.rename(columns={"index_right": "LINZ_parcel_ID"})
geocoding_results.LINZ_parcel_ID = geocoding_results.LINZ_parcel_ID.astype("Int64")
geocoding_results


geocoding_results["number_name"] = df.number_name[geocoding_results.index]
match = geocoding_results[~pd.isna(geocoding_results.number_name) & ~pd.isna(geocoding_results.addresses)].apply(check_match, axis=1)
geocoding_results["LINZ_MATCH_CODE"] = match
geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    40618
1.0     2233
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["LINZ_2ND_MATCH_CODE"] = df.LINZ_2ND_MATCH_CODE[geocoding_results.index]
def find_match_in_range(row):
    if np.isnan(row.LINZ_2ND_MATCH_CODE):
        r = 1250
    else:
        r = 2000
    indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], r)
    potential_matches = parcels.iloc[indices_in_range]
    match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
    if len(match):
        row.LINZ_parcel_ID = match.index[0]
        row.addresses = match.addresses.iloc[0]
        row.full_addresses = match.full_addresses.iloc[0]
        row.LINZ_MATCH_CODE = 2
    return row
matches = geocoding_results[
    ~pd.isna(geocoding_results.number_name) &
    pd.isna(geocoding_results["LINZ_MATCH_CODE"]) &
    geocoding_results.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


geocoding_results.update(matches)
geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    40407
1.0     2233
2.0      211
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["original_LINZ_MATCH_CODE"] = df.LINZ_MATCH_CODE[geocoding_results.index]


geocoding_results


text_match = geocoding_results.original_LINZ_MATCH_CODE.isin([3,6]) & pd.isna(geocoding_results.LINZ_MATCH_CODE)
print(sum(text_match))
geocoding_results = geocoding_results[~text_match]

5773


street_matches = geocoding_results[pd.isna(geocoding_results.LINZ_MATCH_CODE)].progress_apply(check_street_match, axis=1)
display(street_matches.value_counts(dropna=False))
geocoding_results.LINZ_MATCH_CODE.update(street_matches)

NaN    31240
7.0     3394
dtype: int64

/tmp/ipykernel_838276/3798380204.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE.update(street_matches)


geocoding_results.LINZ_MATCH_CODE[
    pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE) &
    ~pd.isna(geocoding_results.LINZ_parcel_ID)] = 8
geocoding_results.LINZ_MATCH_CODE[
    pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_parcel_ID)] = 9

/tmp/ipykernel_838276/2859640296.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE[
/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/pandas/core/generic.py:8870: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  return self._update_inplace(result)
/tmp/ipykernel_838276/2859640296.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE[


geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

8.0    17370
NaN    13347
7.0     3394
1.0     2233
9.0      523
2.0      211
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["OBS"] = df.OBS[geocoding_results.index]
geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    geocoding_results.LINZ_2ND_MATCH_CODE.isin([1,2,4,5])
] = 10
geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) & ~pd.isna(geocoding_results.LINZ_parcel_ID)] = 8

/home/ubuntu/miniconda3/envs/house-upzone/lib/python3.9/site-packages/geopandas/geodataframe.py:1322: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  super(GeoDataFrame, self).__setitem__(key, value)
/tmp/ipykernel_838276/3040101686.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) &
/tmp/ipykernel_838276/3040101686.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) & ~pd.isna(geocoding_results.LINZ_parcel_ID)] = 8


# Actually, this operation is equivalent to +=3
geocoding_results.LINZ_MATCH_CODE.update(geocoding_results.LINZ_MATCH_CODE[geocoding_results.original_LINZ_MATCH_CODE.isin([4,5,6])].replace({1: 4, 2: 5, 3: 6}))

/tmp/ipykernel_838276/4103146720.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  geocoding_results.LINZ_MATCH_CODE.update(geocoding_results.LINZ_MATCH_CODE[geocoding_results.original_LINZ_MATCH_CODE.isin([4,5,6])].replace({1: 4, 2: 5, 3: 6}))


made_it_worse = geocoding_results.LINZ_MATCH_CODE > geocoding_results.original_LINZ_MATCH_CODE
print(sum(made_it_worse))
geocoding_results = geocoding_results[~made_it_worse]

4664


geocoding_results["change"] = geocoding_results.apply(lambda row: f"{row.original_LINZ_MATCH_CODE} -> {row.LINZ_MATCH_CODE}", axis=1)
geocoding_results["change"].value_counts()

8.0 -> 8.0      15171
10.0 -> 10.0    10335
3.0 -> 1.0       1852
7.0 -> 7.0       1371
8.0 -> 7.0       1146
10.0 -> 8.0       992
10.0 -> 7.0       877
6.0 -> 4.0        232
3.0 -> 2.0        150
10.0 -> 1.0       149
9.0 -> 9.0         51
10.0 -> 2.0        43
9.0 -> 8.0         18
6.0 -> 5.0         18
8.0 -> nan          6
7.0 -> nan          2
10.0 -> nan         1
Name: change, dtype: int64


display(geocoding_results.change[pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE)].value_counts())
display(geocoding_results.change[~pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE)].value_counts())

8.0 -> 8.0    13780
3.0 -> 1.0     1534
7.0 -> 7.0     1242
8.0 -> 7.0      984
6.0 -> 4.0      214
3.0 -> 2.0      123
9.0 -> 9.0       51
6.0 -> 5.0       18
9.0 -> 8.0       18
Name: change, dtype: int64

10.0 -> 10.0    10335
8.0 -> 8.0       1391
10.0 -> 8.0       992
10.0 -> 7.0       877
3.0 -> 1.0        318
8.0 -> 7.0        162
10.0 -> 1.0       149
7.0 -> 7.0        129
10.0 -> 2.0        43
3.0 -> 2.0         27
6.0 -> 4.0         18
8.0 -> nan          6
7.0 -> nan          2
10.0 -> nan         1
Name: change, dtype: int64


good_changes = geocoding_results.LINZ_MATCH_CODE < geocoding_results.original_LINZ_MATCH_CODE
print(sum(good_changes))
geocoding_results = geocoding_results[good_changes]
geocoding_results["change"].value_counts()

5477

3.0 -> 1.0     1852
8.0 -> 7.0     1146
10.0 -> 8.0     992
10.0 -> 7.0     877
6.0 -> 4.0      232
3.0 -> 2.0      150
10.0 -> 1.0     149
10.0 -> 2.0      43
6.0 -> 5.0       18
9.0 -> 8.0       18
Name: change, dtype: int64


df.update(geocoding_results)
df


df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0     133138
2.0      51003
8.0      25140
10.0     11244
7.0       9941
3.0       6266
4.0       1706
5.0        831
6.0        285
9.0         97
Name: LINZ_MATCH_CODE, dtype: int64


sum(df.LINZ_MATCH_CODE < df.LINZ_2ND_MATCH_CODE)

611


group = df[~pd.isna(df.LINZ_2ND_MATCH_CODE)].groupby("OBS")
group

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f9025f5c2e0>


updates = []
for ix, sample in tqdm(group):
    minmatch = sample.LINZ_MATCH_CODE.min()
    if minmatch <=2:
        sample.loc[sample.LINZ_MATCH_CODE>2, "LINZ_MATCH_CODE"] = 10
    elif minmatch <=5:
        sample.loc[sample.LINZ_MATCH_CODE>5, "LINZ_MATCH_CODE"] = 10
    sample.LINZ_2ND_MATCH_CODE = minmatch
    updates.append(sample)
updates = pd.concat(updates)
updates


df.update(updates)


assert sum(df.LINZ_MATCH_CODE < df.LINZ_2ND_MATCH_CODE) == 0


df.LINZ_MATCH_CODE.value_counts()

1.0     133138
2.0      51003
8.0      24139
10.0     13689
7.0       9064
3.0       5703
4.0       1702
5.0        831
6.0        285
9.0         97
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_parcel_ID[df.LINZ_MATCH_CODE >= 9] = np.nan
df.addresses[df.LINZ_MATCH_CODE >= 9] = np.nan
df.full_addresses[df.LINZ_MATCH_CODE >= 9] = np.nan

/tmp/ipykernel_838276/3577278218.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df.LINZ_parcel_ID[df.LINZ_MATCH_CODE >= 9] = np.nan
/tmp/ipykernel_838276/3577278218.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df.addresses[df.LINZ_MATCH_CODE >= 9] = np.nan
/tmp/ipykernel_838276/3577278218.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df.full_addresses[df.LINZ_MATCH_CODE >= 9] = np.nan


distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
display(distances)
display(distances[distances>0].describe())

0           0.000000
1           0.000000
2           0.000000
3         272.107738
4         302.608731
             ...    
239644      0.000000
239646      0.000000
239647     12.431048
239648      0.000000
239649      0.000000
Length: 225809, dtype: float64

count     57823.000000
mean       2397.246340
std        8517.809372
min           0.000062
25%          10.607484
50%          35.374714
75%         178.898393
max      399996.638879
dtype: float64


df["distance_to_parcel"] = distances


print(sum((df.LINZ_MATCH_CODE < 7) & (df.addresses.str.len()!=df.full_addresses.str.len())))

308


non_na_parcel_ids = df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]
full_addrs = parcels.full_addresses[non_na_parcel_ids]
full_addrs.index = non_na_parcel_ids.index
df.full_addresses = full_addrs
print(sum((df.LINZ_MATCH_CODE < 7) & (df.addresses.str.len()!=df.full_addresses.str.len())))
display(df)

0


def get_full_addr(row):
    try:
        return row.full_addresses[row.addresses.index(row.number_name)]
    except:
        display(row)
df["matched_address"] = df[df.LINZ_MATCH_CODE < 7].progress_apply(get_full_addr, axis=1)
df


%%time
phase0 = pd.read_csv("output/parcels_phase0.csv", index_col="LINZ_parcel_ID", low_memory=False, lineterminator="\n")
phase0

CPU times: user 14.8 s, sys: 4.22 s, total: 19 s
Wall time: 19 s


len(df), len(df.LINZ_parcel_ID.unique()), len(phase0), len(phase0.index.unique())

(239651, 152990, 547902, 547902)


%%time
df = df.drop(columns="geometry").merge(phase0, on="LINZ_parcel_ID", how="left")
df

CPU times: user 8.49 s, sys: 782 ms, total: 9.27 s
Wall time: 9.26 s


df.columns

Index(['index', 'OBS', 'CONSENT_DATE', 'MARCH_YEAR', 'ADDRESS_1', 'ADDRESS_2',
       'ADDRESS_3', 'LB_Name', 'BUILDING_TYPE_NAME', 'BUILDING_TYPE_CODE',
       'FLOOR_AREA', 'VALUE', 'BUILDINGS', 'Building_Type_Group',
       'Residential_Type', 'Business_Group', 'Business_Category',
       'Type_Class_Year', 'MBCODE', 'MBYEAR', 'AUP_BaseZone',
       'AUP_BaseZone_Group', 'ZONE', 'ZONE_ID', 'ZONE_NAME', 'ZONE_TYPE',
       'BUSINESS_TYPE', 'PlanAreaName', 'SHA_Name_154', 'X_Coordinate',
       'Y_Coordinate', 'Ranged_Address_indicator', 'number_name',
       'full_address', 'LINZ_parcel_ID', 'addresses', 'full_addresses',
       'LINZ_MATCH_CODE', 'distance_to_parcel', 'LINZ_2ND_MATCH_CODE',
       'distance_from_geocoding_result', 'matched_address', 'appellation',
       'affected_surveys', 'parcel_intent', 'topology_type',
       'statutory_actions', 'land_district', 'titles', 'survey_area',
       'calc_area', 'LINZ_parcel_centroid_lon', 'LINZ_parcel_centroid_lat',
       'LINZ_parcel_vertices_lon', 'LINZ_parcel_vertices_lat',
       'LINZ_parcel_roadvertices_lon', 'LINZ_parcel_roadvertices_lat',
       'LINZmatch_AUP_code', 'LINZmatch_AUP_name', 'LINZ_adjoining_parcel_ID',
       'LINZ_parcel_sides_zones', 'LINZ_TRNSPWR_ohead_name',
       'LINZ_TRNSPWR_ohead_indicator', 'LINZ_VWSHFT_ohead_name',
       'LINZ_VWSHFT_ohead_ID', 'LINZ_VWSHFT_ohead_indicator', 'Hdist_rural',
       'Hdist_rural_code', 'Hdist_rural_name', 'Hdist_bus', 'Hdist_bus_code',
       'Hdist_bus_name', 'Hdist_resid', 'Hdist_resid_code', 'Hdist_resid_name',
       'Hdist_SH', 'Hdist_MHS', 'Hdist_MHU', 'Hdist_THA', 'Local_Area_name',
       'SA22018_name', 'SA22018_code', 'AU2013_name', 'AU2013_code',
       'MB2018_code', 'MB2013_code', 'Hdist_coast', 'Hdist_motorway',
       'Hdist_main_road', 'Hdist_rail', 'Hdist_skytower', 'SpHA_indicator'],
      dtype='object')


df.to_csv("restricted/BCs_issued_by_AUP_TLADCs_2021FEB_augmented.csv", index=False)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
0	227614	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	398 PAKIRI BLOCK ROAD	Tomarata	Auckland	Rodney	Houses	1111	231.0	NaN	1	Residential	Detached	NaN	NaN	2014	135900.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.634550	-36.224858	POINT (174.63455 -36.22486)
1	227656	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Ahuroa Road	Puhoi	Auckland	Rodney	Non-building construction	3111	0.0	NaN	1	Other	NaN	NaN	NaN	2014	146701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.568721	-36.475863	POINT (174.56872 -36.47586)
2	227796	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Upper Orewa Road	Upper Orewa	Auckland	Rodney	Townhouses, flats, units, and other dwellings	1129	260.0	NaN	2	Residential	Attached	NaN	NaN	2014	177701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.641586	-36.590190	POINT (174.64159 -36.59019)
3	227896	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	56 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	179.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (174.54174 -36.77819)
4	227902	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	77 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	180.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (174.54174 -36.77819)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207221	151352	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200	0.0	3650000.0	0	Residential	Attached	NaN	NaN	1998	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (174.76031 -36.85153)
207222	151351	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	464	REMUERA RD	REMUERA	Orakei	New Flats	1200	0.0	120000.0	0	Residential	Attached	NaN	NaN	1998	490600.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_28	Residential 7c	Residential	NaN	NaN	NaN	174.801622	-36.880545	POINT (174.80162 -36.88055)
207223	151350	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000.0	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (174.83046 -36.90405)
207224	151349	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Offices	1461	0.0	0.0	1	Business	NaN	Group 2	Offices	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768483	-36.845564	POINT (174.76848 -36.84556)
207225	151348	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Hotels - excludes taverns, hotel bars and ...	1411	8974.0	500000.0	1	Business	NaN	Group 2	Hospitality	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768483	-36.845564	POINT (174.76848 -36.84556)

	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	geometry
id
4918991	Lot 327 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	NA8C/911	642.0	643.0	MULTIPOLYGON (((1764256.381 5917306.269, 17642...
4919914	Lot 178 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	474924, NA8C/768	650.0	651.0	MULTIPOLYGON (((1764569.269 5917546.907, 17645...
4926734	Lot 144 DP 53562	DP 53562	DCDB	Primary	None	North Auckland	386353, 386354, 386355, 386356, NA4D/859	640.0	640.0	MULTIPOLYGON (((1764447.122 5917598.067, 17644...
4926945	Lot 183 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	433646, NA8C/773	913.0	914.0	MULTIPOLYGON (((1764510.216 5917605.992, 17644...
4929034	Lot 260 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	488577, NA8C/850	622.0	624.0	MULTIPOLYGON (((1764653.473 5917395.879, 17646...
...	...	...	...	...	...	...	...	...	...	...
5227472	None	None	Road	Primary	None	North Auckland	None	NaN	1330.0	MULTIPOLYGON (((1766929.317 5907928.650, 17669...
5242816	None	None	Road	Primary	None	North Auckland	None	NaN	1049.0	MULTIPOLYGON (((1766662.352 5908073.993, 17666...
7520053	Lot 5 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	621250	2253.0	2252.0	MULTIPOLYGON (((1766988.975 5907984.681, 17669...
7520049	Lot 1 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	619244	13064.0	13062.0	MULTIPOLYGON (((1766988.975 5907984.681, 17669...
4733845	Part Lot 59 DP 60001	DP 60001	DCDB	Primary	[Referenced] Declaration That State Housing La...	North Auckland	NA86D/103	5449.0	5447.0	MULTIPOLYGON (((1766545.381 5908050.017, 17665...

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses
0	227614	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	398 PAKIRI BLOCK ROAD	Tomarata	Auckland	Rodney	Houses	1111	231.0	NaN	1	Residential	Detached	NaN	NaN	2014	135900.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.634550	-36.224858	POINT (1746904.853 5989872.553)	False	398 pakiri	398 pakiri block road tomarata auckland	5209817	NaN	NaN
1	227656	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Ahuroa Road	Puhoi	Auckland	Rodney	Non-building construction	3111	0.0	NaN	1	Other	NaN	NaN	NaN	2014	146701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.568721	-36.475863	POINT (1740536.028 5962125.407)	False	ahuroa road	ahuroa road puhoi auckland	5227114	NaN	NaN
2	227796	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Upper Orewa Road	Upper Orewa	Auckland	Rodney	Townhouses, flats, units, and other dwellings	1129	260.0	NaN	2	Residential	Attached	NaN	NaN	2014	177701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.641586	-36.590190	POINT (1746847.868 5949333.496)	False	upper orewa	upper orewa road upper orewa auckland	6605265	NaN	NaN
3	227896	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	56 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	179.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	56 vinistra	56 vinistra road huapai auckland	7859219	NaN	NaN
4	227902	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	77 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	180.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	77 vinistra	77 vinistra road huapai auckland	7859219	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207221	151352	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200	0.0	3650000.0	0	Residential	Attached	NaN	NaN	1998	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.812 5920152.000)	True	164-168 hobson	164-168 hobson st auckland	6752833	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...
207222	151351	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	464	REMUERA RD	REMUERA	Orakei	New Flats	1200	0.0	120000.0	0	Residential	Attached	NaN	NaN	1998	490600.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_28	Residential 7c	Residential	NaN	NaN	NaN	174.801622	-36.880545	POINT (1760559.126 5916863.750)	False	464 remuera	464 remuera rd remuera	4928237	[464 remuera, 2/464 remuera, 1/464 remuera, 3b...	[464 Remuera Road, Remuera, Auckland, 2/464 Re...
207223	151350	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000.0	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (1763079.439 5914207.500)	False	55b panorama	55b panorama rd mount wellington	4963967	[55 panorama]	[55 Panorama Road, Mount Wellington, Auckland]
207224	151349	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Offices	1461	0.0	0.0	1	Business	NaN	Group 2	Offices	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768483	-36.845564	POINT (1757677.379 5920800.000)	False	67 customs	67 customs st east auckland central	5030200	[67 customs, 8/67 customs, 7/67 customs, 4/67 ...	[67 Customs Street East, Auckland Central, Auc...
207225	151348	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Hotels - excludes taverns, hotel bars and ...	1411	8974.0	500000.0	1	Business	NaN	Group 2	Hospitality	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768483	-36.845564	POINT (1757677.379 5920800.000)	False	67 customs	67 customs st east auckland central	5030200	[67 customs, 8/67 customs, 7/67 customs, 4/67 ...	[67 Customs Street East, Auckland Central, Auc...

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
3	227896	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	56 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	179.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	56 vinistra	56 vinistra road huapai auckland	8081617	[56 vinistra]	[56 Vinistra Road, Kumeū]	2.0
4	227902	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	77 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111	180.0	NaN	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	77 vinistra	77 vinistra road huapai auckland	8081611	[77 vinistra]	[77 Vinistra Road, Kumeū]	2.0
6	227911	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	753A COATESVILLE-RIVERHEAD HIGHWAY	Riverhead	Auckland	Rodney	Non-building construction	3111	0.0	NaN	1	Other	NaN	NaN	NaN	2014	214502.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.584367	-36.783907	POINT (1741373.805 5927928.444)	False	753a coatesville-riverhead	753a coatesville-riverhead highway riverhead a...	5230689	NaN	NaN	NaN
7	227912	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	21 DIAMOND LANE	Riverhead	Auckland	Rodney	Farm buildings	2711	313.0	NaN	1	Other	NaN	NaN	NaN	2014	216902.0	2013.0	Road	General	27.0	RDC_2	Countryside Living Rural	Rural	NaN	NaN	NaN	174.618681	-36.761155	POINT (1744478.626 5930401.325)	False	21 diamond	21 diamond lane riverhead auckland	7741687	[21 diamond]	[21 Diamond Lane, Riverhead]	2.0
9	227954	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	60 CLARK ROAD	Hobsonville	Auckland	Upper Harbour	Houses	1111	195.0	NaN	1	Residential	Detached	NaN	NaN	2014	222600.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.652342	-36.798024	POINT (1747412.651 5926259.663)	False	60 clark	60 clark road hobsonville auckland	8185386	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207198	151375	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	20A	HOLBROOK ST	AVONDALE	Whau	New (and pre-built) house, unit, bach, crib, b...	1100	241.0	350000.0	1	Residential	Detached	NaN	NaN	1998	378602.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_18	Residential 5	Residential	NaN	NaN	NaN	174.704383	-36.908561	POINT (1751836.997 5913914.750)	False	20a holbrook	20a holbrook st avondale	5086422	[20a holbrook, 20b holbrook]	[20A Holbrook Street, Blockhouse Bay, Auckland...	2.0
207203	151370	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	5B	COMMODORE DR	LYNFIELD	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100	160.0	250000.0	1	Residential	Detached	NaN	NaN	1998	587400.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.720223	-36.926547	POINT (1753212.269 5911893.874)	False	5b commodore	5b commodore dr lynfield	6810857	[5b commodore]	[5B Commodore Drive, Mount Roskill, Auckland]	2.0
207205	151368	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	54	ELLIS AVE	MOUNT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100	158.0	268000.0	1	Residential	Detached	NaN	NaN	1998	584402.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.719375	-36.912989	POINT (1753163.815 5913399.499)	False	54 ellis	54 ellis ave mount roskill	7607279	[54 ellis]	[54 Ellis Avenue, Mount Roskill, Auckland]	2.0
207208	151365	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	1B	WAIRUA RD	REMUERA	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100	127.0	400000.0	1	Residential	Detached	NaN	NaN	1998	494200.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_18	Residential 5	Residential	NaN	NaN	NaN	174.804747	-36.882127	POINT (1760834.375 5916683.000)	False	1b wairua	1b wairua rd remuera	7487630	[1b wairua]	[1B Wairua Road, Remuera, Auckland]	2.0
207223	151350	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000.0	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (1763079.439 5914207.500)	False	55b panorama	55b panorama rd mount wellington	5068248	[55b panorama]	[55B Panorama Road, Mount Wellington, Auckland]	2.0

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
6	227911.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	753A COATESVILLE-RIVERHEAD HIGHWAY	Riverhead	Auckland	Rodney	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	214502.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.584367	-36.783907	POINT (1741373.805 5927928.444)	False	753a coatesville-riverhead	753a coatesville-riverhead highway riverhead a...	5230689	NaN	NaN	NaN
9	227954.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	60 CLARK ROAD	Hobsonville	Auckland	Upper Harbour	Houses	1111.0	195.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	222600.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.652342	-36.798024	POINT (1747412.651 5926259.663)	False	60 clark	60 clark road hobsonville auckland	8185386	NaN	NaN	NaN
18	228723.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	344 FLAT BUSH SCHOOL ROAD	FLAT BUSH	Auckland	Howick	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	713100.0	2013.0	Road	General	27.0	MCC_ROAD	Secondary Road	Other	NaN	NaN	NaN	174.915055	-36.973715	POINT (1770461.566 5906329.777)	False	344 flat	344 flat bush school road flat bush auckland	5209063	NaN	NaN	NaN
21	228822.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	149 GROVE ROAD	PAPAKURA	Auckland	Devonport - Takapuna	Houses	1111.0	100.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	332800.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.804500	-36.819603	POINT (1760943.384 5923620.191)	False	149 grove	149 grove road papakura auckland	5222980	NaN	NaN	NaN
22	228824.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	173 GROVE ROAD	PAPAKURA	Auckland	Devonport - Takapuna	Houses	1111.0	100.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	332800.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.804500	-36.819603	POINT (1760943.384 5923620.191)	False	173 grove	173 grove road papakura auckland	5222980	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207144	151429.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	95.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.311 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
207145	151428.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	120.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.311 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
207146	151427.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	81.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.311 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
207147	151426.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	95.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.311 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
207148	151425.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	81.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.311 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN

INPUTS¶

OUTPUTS¶

	address_id	source_dataset	change_id	full_address_number	full_road_name	full_address	territorial_authority	unit_type	unit_value	level_type	level_value	address_number_prefix	address_number	address_number_suffix	address_number_high	road_name_prefix	road_name	road_type_name	road_suffix	water_name	water_body_name	suburb_locality	town_city	address_class	address_lifecycle	gd2000_xcoord	gd2000_ycoord	road_name_ascii	water_name_ascii	water_body_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry
0	999891	AIMS	887328	10	Barnfield Place	10 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	10	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.651783	-36.891240	Barnfield	None	None	Glendene	Auckland	Barnfield Place	10 Barnfield Place, Glendene, Auckland	POINT (1747183.936 5915918.849)
1	999921	AIMS	887357	11	Barnfield Place	11 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	11	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652010	-36.891441	Barnfield	None	None	Glendene	Auckland	Barnfield Place	11 Barnfield Place, Glendene, Auckland	POINT (1747203.803 5915896.158)
2	999986	AIMS	887418	14	Barnfield Place	14 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	14	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652128	-36.891016	Barnfield	None	None	Glendene	Auckland	Barnfield Place	14 Barnfield Place, Glendene, Auckland	POINT (1747215.175 5915943.129)
3	1000015	AIMS	887447	16	Barnfield Place	16 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	16	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652287	-36.890874	Barnfield	None	None	Glendene	Auckland	Barnfield Place	16 Barnfield Place, Glendene, Auckland	POINT (1747229.606 5915958.610)
4	1000050	AIMS	887473	18	Barnfield Place	18 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	18	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652338	-36.890719	Barnfield	None	None	Glendene	Auckland	Barnfield Place	18 Barnfield Place, Glendene, Auckland	POINT (1747234.400 5915975.823)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
667637	3070850	CADS	70850	27	Wairiki Road	27 Wairiki Road, Mount Eden, Auckland	Auckland	None	None	None	None	None	27	None	NaN	None	Wairiki	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754973	-36.890654	Wairiki	None	None	Mount Eden	Auckland	Wairiki Road	27 Wairiki Road, Mount Eden, Auckland	POINT (1756380.800 5915819.700)
667638	3070851	CADS	70851	44	Wairiki Road	44 Wairiki Road, Mount Eden, Auckland	Auckland	None	None	None	None	None	44	None	NaN	None	Wairiki	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.755018	-36.890903	Wairiki	None	None	Mount Eden	Auckland	Wairiki Road	44 Wairiki Road, Mount Eden, Auckland	POINT (1756384.300 5915792.000)
667639	3075863	CADS	75863	2/50	Ellerton Road	2/50 Ellerton Road, Mount Eden, Auckland	Auckland	None	2	None	None	None	50	None	NaN	None	Ellerton	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754430	-36.892419	Ellerton	None	None	Mount Eden	Auckland	Ellerton Road	2/50 Ellerton Road, Mount Eden, Auckland	POINT (1756328.800 5915624.800)
667640	3075874	CADS	75874	1/15	Douglas Road	1/15 Douglas Road, Mount Eden, Auckland	Auckland	None	1	None	None	None	15	None	NaN	None	Douglas	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754200	-36.889190	Douglas	None	None	Mount Eden	Auckland	Douglas Road	1/15 Douglas Road, Mount Eden, Auckland	POINT (1756314.900 5915983.400)
667641	3075875	CADS	75875	2/15	Douglas Road	2/15 Douglas Road, Mount Eden, Auckland	Auckland	None	2	None	None	None	15	None	NaN	None	Douglas	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754200	-36.889190	Douglas	None	None	Mount Eden	Auckland	Douglas Road	2/15 Douglas Road, Mount Eden, Auckland	POINT (1756314.900 5915983.400)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
10342	212327.0	Mar 01, 2020 12:00:00 AM	Mar 01, 2020 12:00:00 AM	27033/1 Railway Land	Panmure	Auckland	Maungakiekie - Tamaki	Non-building construction	3111.0	0.0	45000.0	1.0	Other	NaN	NaN	NaN	2014.0	628100.0	2013.0	Strategic Transport Corridor Zone	General	26.0	ACC_34	Special Purpose 3	Other	NaN	NaN	NaN	174.849439	-36.898071	POINT (1764783.299 5914837.829)	False	27033/1 railway	27033/1 railway land panmure auckland	5205106	NaN	NaN	NaN
17380	200118.0	Jul 01, 2019 12:00:00 AM	Mar 01, 2020 12:00:00 AM	57F/3 FOREST HILL ROAD	Henderson	Auckland	Henderson - Massey	Houses	1111.0	139.0	287000.0	1.0	Residential	Detached	NaN	NaN	2014.0	281403.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.615666	-36.898033	POINT (1743952.714 5915220.304)	False	57f/3 forest	57f/3 forest hill road henderson auckland	7941070	[3/57f forest]	[3/57F Forest Hill Road, Henderson, Auckland]	NaN
17381	200119.0	Jul 01, 2019 12:00:00 AM	Mar 01, 2020 12:00:00 AM	57F/1 FOREST HILL ROAD	Henderson	Auckland	Henderson - Massey	Houses	1111.0	139.0	287000.0	4.0	Residential	Detached	NaN	NaN	2014.0	281403.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.616060	-36.898146	POINT (1743987.636 5915207.207)	False	57f/1 forest	57f/1 forest hill road henderson auckland	7941072	[1/57f forest]	[1/57F Forest Hill Road, Henderson, Auckland]	NaN
28789	182824.0	Jul 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	1/334 GLENBROOK ROAD	PUKEKOHE	Auckland	Franklin	Houses	1111.0	649.0	1000000.0	1.0	Residential	Detached	NaN	NaN	2014.0	809900.0	2013.0	Rural - Rural Production Zone	Rural	16.0	FDC_11	Rural	Rural	NaN	NaN	NaN	174.849579	-37.149424	POINT (1764253.487 5886949.969)	False	1/334 glenbrook	1/334 glenbrook road pukekohe auckland	7670282	[2 homestead]	[2 Homestead Hill Lane, Kingseat]	NaN
31969	178214.0	Mar 01, 2018 12:00:00 AM	Mar 01, 2018 12:00:00 AM	1/9 Willis Street	Torbay	Auckland	Hibiscus and Bays	Houses	1111.0	205.0	850000.0	1.0	Residential	Detached	NaN	NaN	2014.0	169300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	NSC_542	Residential 4B	Residential	NaN	NaN	NaN	174.752834	-36.694378	POINT (1756589.283 5937598.844)	False	1/9 willis	1/9 willis street torbay auckland	7960428	[9a willis]	[9A Willis Street, Torbay, Auckland]	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
205072	78099.0	Feb 01, 2002 12:00:00 AM	Apr 01, 2002 12:00:00 AM	2/40	ELLIS AVE	MT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100.0	262.0	200000.0	2.0	Residential	Detached	NaN	NaN	1998.0	584402.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.720303	-36.913598	POINT (1753245.253 5913330.470)	False	2/40 ellis	2/40 ellis ave mt roskill	4903343	[40b ellis, 40a ellis, 40 ellis, 42 ellis]	[40B Ellis Avenue, Mount Roskill, Auckland, 40...	NaN
205580	148687.0	Jan 01, 2014 12:00:00 AM	Mar 01, 2014 12:00:00 AM	2/76	GLAMORGAN DR	TORBAY	Hibiscus and Bays	New (and pre-built) house, unit, bach, crib, b...	1100.0	247.0	370500.0	1.0	Residential	Detached	NaN	NaN	1998.0	165300.0	2013.0	Residential - Single House Zone	Residential	19.0	NSC_542	Residential 4B	Residential	NaN	NaN	NaN	174.742102	-36.701771	POINT (1755615.610 5936796.169)	False	2/76 glamorgan	2/76 glamorgan dr torbay	5189573	[76b glamorgan, 76c glamorgan, 76 glamorgan, 7...	[76B Glamorgan Drive, Torbay, Auckland, 76C Gl...	NaN
206204	150759.0	May 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	2/55	ARAWA ST	NaN	Whau	New (and pre-built) house, unit, bach, crib, b...	1100.0	225.0	320000.0	1.0	Residential	Detached	NaN	NaN	1998.0	269400.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	WCC_8	Living 1	Residential	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.670965	-36.910209	POINT (1748856.499 5913784.605)	False	2/55 arawa	2/55 arawa st	8132319	[55a arawa]	[55A Arawa Street, New Lynn, Auckland]	NaN
206234	150729.0	May 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	3B/12	SEAWAY RD	NaN	Franklin	New (and pre-built) house, unit, bach, crib, b...	1100.0	23.0	45000.0	1.0	Residential	Detached	NaN	NaN	1998.0	810605.0	2013.0	Business - Neighbourhood Centre Zone	Business	44.0	FDC_19	Village Business	Business	Commercial	NaN	NaN	174.701889	-37.135781	POINT (1751163.814 5888709.273)	False	3b/12 seaway	3b/12 seaway rd	6728635	[14 seaway, 12 seaway]	[14 Seaway Road, Clarks Beach, 12 Seaway Road,...	NaN
206699	151875.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	2/42A	SEAVIEW RD	CASTOR BAY	Devonport - Takapuna	New (and pre-built) house, unit, bach, crib, b...	1100.0	377.0	590000.0	1.0	Residential	Detached	NaN	NaN	1998.0	313400.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	NSC_541	Residential 4A	Residential	NaN	NaN	NaN	174.761845	-36.764401	POINT (1757251.438 5929815.559)	False	2/42a seaview	2/42a seaview rd castor bay	5176247	[42 seaview]	[42 Seaview Road, Castor Bay, Auckland]	NaN

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
362	228135.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	31F FRASER AVENUE	Northcote	Auckland	Kaipatiki	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	364900.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	NSC_541	Residential 4A	Residential	NaN	Northcote Strategic Area	Northcote Strategic Area	174.747281	-36.797080	POINT (1755885.243 5926213.817)	False	31f fraser	31f fraser avenue northcote auckland	8067855	[31 fraser]	[31 Fraser Avenue, Northcote, Auckland]	NaN
466	228408.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	2A TRURO ROAD	Sandringham	Auckland	Albert - Eden	Apartments	1121.0	0.0	NaN	0.0	Residential	Attached	NaN	NaN	2014.0	537200.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.739554	-36.882790	POINT (1755022.621 5916717.367)	False	2a truro	2a truro road sandringham auckland	7478849	[102/2a truro, 502/2a truro, 501/2a truro, 402...	[102/2A Truro Road, Sandringham, Auckland, 502...	NaN
604	228653.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	125C MURPHYS ROAD	Flat Bush	Auckland	Howick	Townhouses, flats, units, and other dwellings	1129.0	98.0	NaN	1.0	Residential	Attached	NaN	NaN	2014.0	713501.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	MCC_S1	Future Development Stage	Special	NaN	Flat Bush Strategic Area	Flat Bush Strategic Area	174.921127	-36.984668	POINT (1770977.570 5905103.596)	False	125c murphys	125c murphys road flat bush auckland	7849289	[121 murphys]	[121 Murphys Road, Flat Bush, Auckland]	NaN
605	228654.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	125C MURPHYS ROAD	Flat Bush	Auckland	Howick	Townhouses, flats, units, and other dwellings	1129.0	0.0	NaN	0.0	Residential	Attached	NaN	NaN	2014.0	713501.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	MCC_S1	Future Development Stage	Special	NaN	Flat Bush Strategic Area	Flat Bush Strategic Area	174.921127	-36.984668	POINT (1770977.570 5905103.596)	False	125c murphys	125c murphys road flat bush auckland	7849289	[121 murphys]	[121 Murphys Road, Flat Bush, Auckland]	NaN
606	228655.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	125C MURPHYS ROAD	Flat Bush	Auckland	Howick	Townhouses, flats, units, and other dwellings	1129.0	0.0	NaN	0.0	Residential	Attached	NaN	NaN	2014.0	713501.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	MCC_S1	Future Development Stage	Special	NaN	Flat Bush Strategic Area	Flat Bush Strategic Area	174.921127	-36.984668	POINT (1770977.570 5905103.596)	False	125c murphys	125c murphys road flat bush auckland	7849289	[121 murphys]	[121 Murphys Road, Flat Bush, Auckland]	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207088	151485.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	147.0	183000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.998 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
207113	151460.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	151.0	215000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.998 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
207116	151457.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	154.0	198000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.998 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
207120	151453.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	154.0	198000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.998 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
207154	151419.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	253S	BUCKLAND RD	MANGERE EAST	Mangere - Otahuhu	New Schools - excludes school hostels	1431.0	685.0	2362000.0	1.0	Other	NaN	NaN	NaN	1998.0	735100.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.818644	-36.967455	POINT (1761892.306 5907192.496)	False	253s buckland	253s buckland rd mangere east	4852120	NaN	NaN	NaN

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel
12	228134.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	7-19 TONAR STREET	Northcote	Auckland	Kaipatiki	Apartments	1121.0	2905.0	NaN	29.0	Residential	Attached	NaN	NaN	2014.0	364300.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	NSC_5611	Residential 6A1	Residential	NaN	Northcote Strategic Area	Northcote Strategic Area	174.747240	-36.795608	POINT (1755884.597 5926377.252)	True	7-19 tonar	7-19 tonar street northcote auckland	8090222	[19b tonar, 19a tonar, 15 tonar]	[19B Tonar Street, Northcote, Auckland, 19A To...	7.0	0.0
13	228236.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	74-80 Wellesley Street West	Auckland Central	Auckland	Waitemata	Hotels, motels, and other short-term accommoda...	2121.0	0.0	NaN	1.0	Business	NaN	Group 2	Hospitality	2014.0	434101.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_29	Victoria Quarter D	Business	Commercial	NaN	NaN	174.759814	-36.849841	POINT (1756895.713 5920339.797)	True	74-80 wellesley	74-80 wellesley street west auckland central a...	5127693	[78 wellesley]	[78 Wellesley Street West, Auckland Central, A...	7.0	0.0
14	228263.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	469-471 Parnell Road	Parnell	Auckland	Waitemata	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	452100.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_27	Residential 7b	Residential	NaN	NaN	NaN	174.782298	-36.860785	POINT (1758877.787 5919088.400)	True	469-471 parnell	469-471 parnell road parnell auckland	4789405	[471 parnell, 469 parnell]	[471 Parnell Road, Parnell, Auckland, 469 Parn...	7.0	0.0
15	228338.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	17-19 TUHAERE STREET	Orakei	Auckland	Orakei	Houses	1111.0	450.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	496600.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_20	Residential 2b	Residential	NaN	NaN	NaN	174.808929	-36.853044	POINT (1761268.211 5919902.539)	True	17-19 tuhaere	17-19 tuhaere street orakei auckland	4805914	[17 tuhaere]	[17 Tuhaere Street, Orakei, Auckland]	7.0	0.0
910	226714.0	Jan 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	39-41 BRUCE MCLAREN ROAD	Henderson	Auckland	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129.0	28.0	NaN	1.0	Residential	Attached	NaN	NaN	2014.0	242501.0	2013.0	Business - Light Industry Zone	Business	17.0	WCC_19	Working	Business	Industrial	NaN	NaN	174.628708	-36.893463	POINT (1745123.508 5915707.499)	True	39-41 bruce	39-41 bruce mclaren road henderson auckland	4870711	[43 bruce, 1/43 bruce]	[43 Bruce McLaren Road, Henderson, Auckland, 1...	7.0	0.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
207053	151520.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	2-6	TITIRANGI RD	NEW LYNN	Whau	New Shops	1451.0	0.0	10100000.0	0.0	Business	NaN	Group 2	Retail	1998.0	264901.0	2013.0	Business - Metropolitan Centre Zone	Business	10.0	WCC_3	Community	Business	Commercial	NaN	NaN	174.677785	-36.910257	POINT (1749463.952 5913768.524)	True	2-6 titirangi	2-6 titirangi rd new lynn	4882242	[10 titirangi]	[10 Titirangi Road, New Lynn, Auckland]	7.0	0.0
207065	151508.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	433-435	HOBSONVILLE RD	HOBSONVILLE	Upper Harbour	New (and pre-built) house, unit, bach, crib, b...	1100.0	206.0	288680.0	1.0	Residential	Detached	NaN	NaN	1998.0	221907.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.654389	-36.794236	POINT (1747602.556 5926676.720)	True	433-435 hobsonville	433-435 hobsonville rd hobsonville	7447549	[70 hobsonville]	[70 Hobsonville Point Road, Hobsonville, Auckl...	7.0	0.0
207216	151357.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	9-11	FENTON ST	EDEN TERRACE	Waitemata	New Flats	1200.0	3760.0	3000000.0	37.0	Residential	Attached	NaN	NaN	1998.0	419702.0	2013.0	Business - Mixed Use Zone	Business	12.0	ACC_10	Business MU	Business	Commercial	NaN	NaN	174.757447	-36.868315	POINT (1756646.878 5918294.000)	True	9-11 fenton	9-11 fenton st eden terrace	7523952	[501/11 fenton, 401/11 fenton, 402/11 fenton, ...	[501/11 Fenton Street, Mount Eden, Auckland, 4...	7.0	0.0
207218	151355.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	138A-138C	GOWING DR	MEADOWBANK	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100.0	214.0	390000.0	1.0	Residential	Detached	NaN	NaN	1998.0	461300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.838299	-36.867962	POINT (1763854.987 5918197.479)	True	138a-138c gowing	138a-138c gowing dr meadowbank	7533935	NaN	NaN	8.0	0.0
207221	151352.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.812 5920152.000)	True	164-168 hobson	164-168 hobson st auckland	6752833	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	7.0	0.0

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel	LINZ_2ND_MATCH_CODE
0	227614.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	398 PAKIRI BLOCK ROAD	Tomarata	Auckland	Rodney	Houses	1111.0	231.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	135900.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.634550	-36.224858	POINT (1746904.853 5989872.553)	False	398 pakiri	398 pakiri block road tomarata auckland	5209817	NaN	NaN	8.0	0.000000	NaN
1	227656.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Ahuroa Road	Puhoi	Auckland	Rodney	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	146701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.568721	-36.475863	POINT (1740536.028 5962125.407)	False	ahuroa road	ahuroa road puhoi auckland	5227114	NaN	NaN	8.0	0.000000	NaN
2	227796.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	Upper Orewa Road	Upper Orewa	Auckland	Rodney	Townhouses, flats, units, and other dwellings	1129.0	260.0	NaN	2.0	Residential	Attached	NaN	NaN	2014.0	177701.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.641586	-36.590190	POINT (1746847.868 5949333.496)	False	upper orewa	upper orewa road upper orewa auckland	6605265	NaN	NaN	8.0	0.000000	NaN
3	227896.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	56 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111.0	179.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	56 vinistra	56 vinistra road huapai auckland	8081617	[56 vinistra]	[56 Vinistra Road, Kumeū]	2.0	272.107738	NaN
4	227902.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	77 VINISTRA ROAD	Huapai	Auckland	Rodney	Houses	1111.0	180.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.541738	-36.778192	POINT (1737579.970 5928624.665)	False	77 vinistra	77 vinistra road huapai auckland	8081611	[77 vinistra]	[77 Vinistra Road, Kumeū]	2.0	302.608731	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
151357.0_11	151357.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	9-11	FENTON ST	EDEN TERRACE	Waitemata	New Flats	1200.0	3760.0	3000000.0	37.0	Residential	Attached	NaN	NaN	1998.0	419702.0	2013.0	Business - Mixed Use Zone	Business	12.0	ACC_10	Business MU	Business	Commercial	NaN	NaN	174.757447	-36.868315	POINT (1756646.878 5918294.000)	True	11 fenton	9-11 fenton st eden terrace	7523952.0	[501/11 fenton, 401/11 fenton, 402/11 fenton, ...	[501/11 Fenton Street, Mount Eden, Auckland, 4...	1.0	0.000000	1.0
151355.0_138	151355.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	138A-138C	GOWING DR	MEADOWBANK	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100.0	214.0	390000.0	1.0	Residential	Detached	NaN	NaN	1998.0	461300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.838299	-36.867962	POINT (1763854.987 5918197.479)	True	138 gowing	138a-138c gowing dr meadowbank	7445958	[138 gowing]	[138 Gowing Drive, Meadowbank, Auckland]	2.0	0.000000	2.0
151352.0_164	151352.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.812 5920152.000)	True	164 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	1.0	0.000000	1.0
151352.0_166	151352.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.812 5920152.000)	True	166 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	1.0	0.000000	1.0
151352.0_168	151352.0	Jul 01, 2014 12:00:00 AM	Mar 01, 2015 12:00:00 AM	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.812 5920152.000)	True	168 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	10.0	0.000000	1.0

	place_id	licence	osm_type	osm_id	boundingbox	lat	lon	display_name	place_rank	category	type	importance
0	2257092.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	1.771129e+08	[-36.2314861, -36.2018597, 174.6324805, 174.68...	-36.2082367	174.6488556	Pakiri Block Road, Tomarata, Rodney, Auckland,...	26.0	highway	unclassified	0.600
1	2237005.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	5.928942e+07	[-36.5117758, -36.5101989, 174.6600882, 174.66...	-36.5110662	174.6606005	Ahuroa Road, Puhoi, Rodney, Auckland, 0951, Ne...	26.0	highway	secondary	0.500
2	2268411.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	1.584750e+08	[-36.5987791, -36.5916888, 174.6506923, 174.65...	-36.5949225	174.6511367	Upper Orewa Road, Rodney, Auckland, 0992, New ...	26.0	highway	tertiary	0.700
6	2031550.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	node	8.562562e+09	[-36.7495572, -36.7494572, 174.6176897, 174.61...	-36.7495072	174.6177397	753A, Coatesville-Riverhead Highway, Deacon Po...	30.0	place	house	0.601
8	2275414.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	2.558329e+08	[-36.8368187, -36.8292264, 174.4340308, 174.43...	-36.8329595	174.4345688	Oaia Road, Muriwai Beach, Rodney, Auckland, 08...	26.0	highway	tertiary	0.600
...	...	...	...	...	...	...	...	...	...	...	...	...
239639	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
239643	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
239644	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
239645	2244421.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	7.638381e+06	[-36.8684516, -36.8681022, 174.7563297, 174.75...	-36.8682559	174.7569026	Fenton Street, Eden Terrace, Auckland, Waitema...	26.0	highway	unclassified	0.500
239650	2192207.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	node	8.654487e+09	[-36.8514673, -36.8513673, 174.7602459, 174.76...	-36.8514173	174.7602959	168, Hobson Street, Aotea Arts Quarter, Auckla...	30.0	place	house	0.411

	index	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel	LINZ_2ND_MATCH_CODE	distance_from_geocoding_result
227904	112.0_137	112.0	Apr 01, 1991 12:00:00 AM	Apr 01, 1992 12:00:00 AM	137-139	SOLAR RD	GLEN EDEN	Waitakere Ranges	New (and pre-built) house, unit, bach, crib, b...	1100.0	97.0	60685.0	1.0	Residential	Detached	NaN	NaN	1998.0	278406.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.630125	-36.914665	POINT (1745209.590 5913353.158)	True	137 solar	137 solar rd glen eden	5118671.0	[139 solar]	[139 Solar Road, Glen Eden, Auckland]	10.0	0.000000	1.0	NaN
227905	112.0_139	112.0	Apr 01, 1991 12:00:00 AM	Apr 01, 1992 12:00:00 AM	137-139	SOLAR RD	GLEN EDEN	Waitakere Ranges	New (and pre-built) house, unit, bach, crib, b...	1100.0	97.0	60685.0	1.0	Residential	Detached	NaN	NaN	1998.0	278406.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.630125	-36.914665	POINT (1745209.590 5913353.158)	True	139 solar	139 solar rd glen eden	5118671.0	[139 solar]	[139 Solar Road, Glen Eden, Auckland]	1.0	0.000000	1.0	NaN
227902	128.0_109	128.0	Apr 01, 1991 12:00:00 AM	Apr 01, 1992 12:00:00 AM	109-111	HUTCHINSON AVE	NEW LYNN	Whau	New Flats	1200.0	85.0	85000.0	1.0	Residential	Attached	NaN	NaN	1998.0	262001.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.671724	-36.918903	POINT (1748907.184 5912818.837)	True	109 hutchinson	109 hutchinson ave new lynn	4852262	[109 hutchinson]	[109 Hutchinson Avenue, New Lynn, Auckland]	2.0	253.058177	2.0	NaN
227903	128.0_111	128.0	Apr 01, 1991 12:00:00 AM	Apr 01, 1992 12:00:00 AM	109-111	HUTCHINSON AVE	NEW LYNN	Whau	New Flats	1200.0	85.0	85000.0	1.0	Residential	Attached	NaN	NaN	1998.0	262001.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.671724	-36.918903	POINT (1748907.184 5912818.837)	True	111 hutchinson	111 hutchinson ave new lynn	5234122	NaN	NaN	10.0	0.000000	2.0	660.403751
227900	282.0_11	282.0	Apr 01, 1991 12:00:00 AM	Apr 01, 1992 12:00:00 AM	11-13	MARION AVE	MT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100.0	123.0	134156.0	1.0	Residential	Detached	NaN	NaN	1998.0	579300.0	2013.0	Business - Light Industry Zone	Business	17.0	ACC_4	Business 4	Business	Commercial	NaN	NaN	174.730030	-36.910799	POINT (1754117.445 5913625.292)	True	11 marion	11 marion ave mt roskill	4928143	[11 marion, 1/11 marion, 2/11 marion]	[11 Marion Avenue, Mount Roskill, Auckland, 1/...	2.0	164.829714	2.0	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
201638	228236.0_80	228236.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	74-80 Wellesley Street West	Auckland Central	Auckland	Waitemata	Hotels, motels, and other short-term accommoda...	2121.0	0.0	NaN	1.0	Business	NaN	Group 2	Hospitality	2014.0	434101.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_29	Victoria Quarter D	Business	Commercial	NaN	NaN	174.759814	-36.849841	POINT (1756895.713 5920339.797)	True	80 wellesley	80 wellesley street west auckland central auck...	5127693.0	[78 wellesley]	[78 Wellesley Street West, Auckland Central, A...	10.0	0.000000	1.0	273.637031
201639	228263.0_469	228263.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	469-471 Parnell Road	Parnell	Auckland	Waitemata	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	452100.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_27	Residential 7b	Residential	NaN	NaN	NaN	174.782298	-36.860785	POINT (1758877.787 5919088.400)	True	469 parnell	469 parnell road parnell auckland	4789405	[471 parnell, 469 parnell]	[471 Parnell Road, Parnell, Auckland, 469 Parn...	1.0	0.000000	1.0	NaN
201640	228263.0_471	228263.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	469-471 Parnell Road	Parnell	Auckland	Waitemata	Non-building construction	3111.0	0.0	NaN	1.0	Other	NaN	NaN	NaN	2014.0	452100.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_27	Residential 7b	Residential	NaN	NaN	NaN	174.782298	-36.860785	POINT (1758877.787 5919088.400)	True	471 parnell	471 parnell road parnell auckland	4789405	[471 parnell, 469 parnell]	[471 Parnell Road, Parnell, Auckland, 469 Parn...	1.0	0.000000	1.0	NaN
201641	228338.0_17	228338.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	17-19 TUHAERE STREET	Orakei	Auckland	Orakei	Houses	1111.0	450.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	496600.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_20	Residential 2b	Residential	NaN	NaN	NaN	174.808929	-36.853044	POINT (1761268.211 5919902.539)	True	17 tuhaere	17 tuhaere street orakei auckland	4805914.0	[17 tuhaere]	[17 Tuhaere Street, Orakei, Auckland]	1.0	0.000000	1.0	NaN
201642	228338.0_19	228338.0	Feb 01, 2021 12:00:00 AM	Mar 01, 2020 12:00:00 AM	17-19 TUHAERE STREET	Orakei	Auckland	Orakei	Houses	1111.0	450.0	NaN	1.0	Residential	Detached	NaN	NaN	2014.0	496600.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_20	Residential 2b	Residential	NaN	NaN	NaN	174.808929	-36.853044	POINT (1761268.211 5919902.539)	True	19 tuhaere	19 tuhaere street orakei auckland	4934129.0	[19 tuhaere]	[19 Tuhaere Street, Orakei, Auckland]	2.0	1.336673	1.0	NaN

	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	LINZ_parcel_centroid_lon	LINZ_parcel_centroid_lat	LINZ_parcel_vertices_lon	LINZ_parcel_vertices_lat	LINZ_parcel_roadvertices_lon	LINZ_parcel_roadvertices_lat	LINZmatch_AUP_code	LINZmatch_AUP_name	LINZ_adjoining_parcel_ID	LINZ_parcel_sides_zones	LINZ_TRNSPWR_ohead_name	LINZ_TRNSPWR_ohead_indicator	LINZ_VWSHFT_ohead_name	LINZ_VWSHFT_ohead_ID	LINZ_VWSHFT_ohead_indicator	Hdist_rural	Hdist_rural_code	Hdist_rural_name	Hdist_bus	Hdist_bus_code	Hdist_bus_name	Hdist_resid	Hdist_resid_code	Hdist_resid_name	Hdist_SH	Hdist_MHS	Hdist_MHU	Hdist_THA	Local_Area_name	SA22018_name	SA22018_code	AU2013_name	AU2013_code	MB2018_code	MB2013_code	Hdist_coast	Hdist_motorway	Hdist_main_road	Hdist_rail	Hdist_skytower	SpHA_indicator
LINZ_parcel_ID
4918991	Lot 327 DP 56341	DP 56341	DCDB	Primary	NaN	North Auckland	NA8C/911	642.0	643.0	174.843218	-36.876042	[174.842994; 174.843152; 174.84337; 174.843471...	[-36.875922; -36.875957; -36.876006; -36.87602...	[174.842994; 174.842997]	[-36.875922; -36.876074]	18.0	Residential - Mixed Housing Suburban Zone	[5061331, 5002265, 5228435, 5061335, 4722548, ...	['18', '18', '27', '18', '18', '18']	NaN	0	NaN	NaN	0	7228.891309	3	Rural - Countryside Living Zone	96.953993	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1499.258244	0.000000	463.752596	342.351869	Orakei	Saint Johns East	144500.0	St Johns	517201	4006757.0	465102	1626.866018	3904.389673	30.431054	675.367028	7850.889144	False
4919914	Lot 178 DP 56342	DP 56342	DCDB	Primary	NaN	North Auckland	474924, NA8C/768	650.0	651.0	174.846572	-36.873551	[174.84645; 174.846486; 174.846701; 174.846648]	[-36.873699; -36.873386; -36.873407; -36.873721]	[174.846486; 174.846701]	[-36.873386; -36.873407]	18.0	Residential - Mixed Housing Suburban Zone	[4789870, 4877424, 5005927, 4804873, 5178022, ...	['18', '18', '18', '18', '18', '27']	NaN	0	NaN	NaN	0	7571.289947	3	Rural - Countryside Living Zone	219.321245	17	Business - Light Industry Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1504.842901	0.000000	380.053451	411.291837	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1666.931163	4307.322450	275.948865	296.455456	8026.797757	False
4926734	Lot 144 DP 53562	DP 53562	DCDB	Primary	NaN	North Auckland	386353, 386354, 386355, 386356, NA4D/859	640.0	640.0	174.845299	-36.873269	[174.845069; 174.845157; 174.845513; 174.84546]	[-36.87326; -36.873123; -36.87327; -36.873421]	[174.845069; 174.845157]	[-36.87326; -36.873123]	18.0	Residential - Mixed Housing Suburban Zone	[5236861, 5048379, 4926945, 4797737]	['27', '18', '18', '18']	NaN	0	NaN	NaN	0	7574.099005	3	Rural - Countryside Living Zone	197.076711	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1551.382113	0.000000	336.510990	468.042305	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1556.501519	4232.755849	170.250936	317.577857	7909.576996	False
4926945	Lot 183 DP 56342	DP 56342	DCDB	Primary	NaN	North Auckland	433646, NA8C/773	913.0	914.0	174.845585	-36.873350	[174.845775; 174.84557; 174.845408; 174.84546;...	[-36.873177; -36.873603; -36.87357; -36.873421...	[174.845775; 174.845575; 174.845689]	[-36.873177; -36.873095; -36.873142]	18.0	Residential - Mixed Housing Suburban Zone	[5233326, 5048379, 4797737, 5054505, 7342193, ...	['27', '18', '18', '18', '18', '18', '18', '18']	NaN	0	NaN	NaN	0	7556.977156	3	Rural - Countryside Living Zone	180.285687	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1529.480918	0.000000	333.915495	445.926909	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1582.400173	4248.261840	195.278997	314.544307	7936.587996	False
4929034	Lot 260 DP 56341	DP 56341	DCDB	Primary	NaN	North Auckland	488577, NA8C/850	622.0	624.0	174.847627	-36.874955	[174.847427; 174.847674; 174.847826; 174.84757...	[-36.875045; -36.874775; -36.874864; -36.87513...	[174.847674; 174.847826]	[-36.874775; -36.874864]	18.0	Residential - Mixed Housing Suburban Zone	[4860765, 5186671, 5186670, 5057572, 5262615]	['18', '18', '18', '18', '27']	NaN	0	NaN	NaN	0	7452.133541	3	Rural - Countryside Living Zone	162.913359	17	Business - Light Industry Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1580.494775	0.000000	489.570672	263.495431	Orakei	Saint Johns East	144500.0	St Johns	517201	464600.0	464600	1834.490817	4299.136620	434.784957	395.754035	8169.879981	False
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
5227472	NaN	NaN	Road	Primary	NaN	North Auckland	NaN	NaN	1330.0	174.874361	-36.959668	[174.875038; 174.874961; 174.874884; 174.87480...	[-36.959943; -36.959973; -36.960003; -36.96003...	[174.875038; 174.874961; 174.874884; 174.87480...	[-36.959943; -36.959973; -36.960003; -36.96003...	27.0	Road	[5214220, 4906751, 5142410, 4891625, 5256195, ...	['27', '17', '17', '17', '27', '17', '17', '17...	NaN	0	NaN	NaN	0	3963.447367	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	86.562395	8	Residential - Terrace Housing and Apartment Bu...	760.946689	239.919686	387.444562	86.562395	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	983.514550	658.806820	45.095107	2934.237955	15888.342992	False
5242816	NaN	NaN	Road	Primary	NaN	North Auckland	NaN	NaN	1049.0	174.872269	-36.958674	[174.872009; 174.872304; 174.872476; 174.87240...	[-36.958681; -36.958453; -36.958569; -36.95863...	[174.872009; 174.872304; 174.872476; 174.87240...	[-36.958681; -36.958453; -36.958569; -36.95863...	27.0	Road	[4733845, 5231272, 4866075, 4785948, 5234594, ...	['64', '27', '33', '32', '27', '27', '8']	NaN	0	NaN	NaN	0	4211.055507	3	Rural - Countryside Living Zone	82.178171	17	Business - Light Industry Zone	0.000000	8	Residential - Terrace Housing and Apartment Bu...	785.375433	262.434622	328.712745	0.000000	Otara	Otara Central	154400.0	Otara North	523501	700601.0	700601	801.720167	611.330344	5.025442	2831.983258	15685.595260	False
7520053	Lot 5 DP 465319	DP 465319	Fee Simple Title	Primary	NaN	North Auckland	621250	2253.0	2252.0	174.875600	-36.959503	[174.875695; 174.875801; 174.876171; 174.87630...	[-36.959428; -36.959472; -36.959629; -36.95968...	[174.875149; 174.875145; 174.875141; 174.87513...	[-36.959472; -36.959456; -36.95944; -36.959424...	17.0	Business - Light Industry Zone	[5214220, 7520050, 7520049]	['27', '17', '17']	['Otahuhu - Whakamaru C']	1	NaN	NaN	0	3911.904824	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	150.152069	8	Residential - Terrace Housing and Apartment Bu...	845.357322	197.645867	386.422868	150.152069	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	905.449427	757.841845	137.798477	3038.063157	15943.954833	False
7520049	Lot 1 DP 465319	DP 465319	Fee Simple Title	Primary	NaN	North Auckland	619244	13064.0	13062.0	174.876401	-36.959399	[174.875695; 174.875635; 174.875514; 174.87543...	[-36.959428; -36.959402; -36.959351; -36.95931...	[174.875187; 174.875207; 174.875323; 174.87533...	[-36.959212; -36.959182; -36.959006; -36.95899...	17.0	Business - Light Industry Zone	[7520050, 5214220, 7520053, 7520052]	['17', '27', '17', '17']	['Otahuhu - Whakamaru C']	1	NaN	NaN	0	3808.115618	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	139.072671	18	Residential - Mixed Housing Suburban Zone	870.886671	139.072671	274.592516	143.276761	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	859.190377	823.461575	202.451909	3105.040962	15980.395818	False
4733845	Part Lot 59 DP 60001	DP 60001	DCDB	Primary	[Referenced] Declaration That State Housing La...	North Auckland	NA86D/103	5449.0	5447.0	174.871298	-36.958743	[174.870701; 174.870815; 174.870818; 174.87082...	[-36.958917; -36.958509; -36.9585; -36.958492;...	[174.870701; 174.870815; 174.870818; 174.87082...	[-36.958917; -36.958509; -36.9585; -36.958492;...	64.0	Special Purpose - Tertiary Education Zone	[5187557, 4758156, 5254506, 4866075, 5225006, ...	['33', '64', '27', '33', '27', '27', '27', '27']	NaN	0	NaN	NaN	0	4250.207218	3	Rural - Countryside Living Zone	124.492091	17	Business - Light Industry Zone	20.115492	8	Residential - Terrace Housing and Apartment Bu...	751.195267	304.268175	310.515280	20.115492	Otara	Otara Central	154400.0	Otara North	523501	700700.0	700700	769.844893	561.066825	37.791191	2753.967151	15637.574122	False

Phase 1: Residential Building Consent Dataset¶

INPUTS¶

OUTPUTS¶